set hive.exec.dynamic.partition=true;
set hive.exec.dynamic.partition.mode='nonstrict';
set hive.exec.max.dynamic.partitions=400;
set hive.exec.max.dynamic.partitions.pernode=400;

insert overwrite table jms_dm.dm_arbit_trans_duty_detail_dt
select
     main.waybill_no                       --运单号
    ,main.order_source_code                --订单来源编码
    ,main.order_source_name                --订单来源名称
    ,main.duty_type                        --责任类型
    ,main.duty_main_code                   --责任主体编码
    ,main.duty_main_name                   --责任主体名称
    ,main.duty_main_type                   --责任主体类型
    ,main.duty_main_agent_code             --责任代理区编码
    ,main.duty_main_agent_name             --责任代理区名称
    ,main.duty_provider_code               --责任省份编码
    ,main.duty_provider_desc               --责任省份名称
    ,main.static_scan_time                 --规划扫描时间
    ,main.dynamic_scan_time                --实际扫描时间
    ,main.duty_shipment_no                 --责任任务号
    ,main.duty_shipment_name               --责任任务名称
    ,main.start_network_code               --始发网点编码
    ,main.start_network_name               --始发网点名称
    ,main.start_city_code                  --始发城市编码
    ,main.start_city_name                  --始发城市名称
    ,main.start_provider_code              --始发省份编码
    ,main.start_provider_name              --始发省份名称
    ,main.start_fran_code                  --始发加盟商编码
    ,main.start_fran_name                  --始发加盟商名称
    ,main.start_agent_code                 --始发代理区编码
    ,main.start_agent_name                 --始发代理区名称
    ,main.start_center_code                --始发中心编码
    ,main.start_center_name                --始发中心名称
    ,main.end_network_code                 --目的网点编码
    ,main.end_network_name                 --目的网点名称
    ,main.end_city_code                    --目的城市编码
    ,main.end_city_name                    --目的城市名称
    ,main.end_provider_code                --目的省份编码
    ,main.end_provider_name                --目的省份名称
    ,main.end_fran_code                    --目的加盟商编码
    ,main.end_fran_name                    --目的加盟商名称
    ,main.end_agent_code                   --目的代理区编码
    ,main.end_agent_name                   --目的代理区名称
    ,main.end_center_code                  --目的中心编码
    ,main.end_center_name                  --目的中心名称
    ,main.aging_sign_time                  --时效签收时间
    ,main.actual_sign_time                 --实际签收时间
    ,main.plan_sign_time
    ,main.delay_day
    ,main.duty_main_id
    ,main.duty_main_agent_id
    ,main.start_network_id
    ,main.start_agent_id
    ,main.port_type   --1进港,0出港
    ,main.center_type --中心类型1始发中心,2中转中心,3目的中心
    ,main.is_tyd
    ,main.is_ntzk
    ,main.dt
from (
    select
         main.waybill_no                       --运单号
        ,main.order_source_code                --订单来源编码
        ,main.order_source_name                --订单来源名称
        ,main.duty_type                        --责任类型
        ,main.duty_main_code                   --责任主体编码
        ,main.duty_main_name                   --责任主体名称
        ,main.duty_main_type                   --责任主体类型
        ,main.duty_main_agent_code             --责任代理区编码
        ,main.duty_main_agent_name             --责任代理区名称
        ,main.duty_provider_code               --责任省份编码
        ,main.duty_provider_desc               --责任省份名称
        ,main.static_scan_time                 --规划扫描时间
        ,main.dynamic_scan_time                --实际扫描时间
        ,main.duty_shipment_no                 --责任任务号
        ,main.duty_shipment_name               --责任任务名称
        ,main.start_network_code               --始发网点编码
        ,main.start_network_name               --始发网点名称
        ,main.start_city_code                  --始发城市编码
        ,main.start_city_name                  --始发城市名称
        ,main.start_provider_code              --始发省份编码
        ,main.start_provider_name              --始发省份名称
        ,main.start_fran_code                  --始发加盟商编码
        ,main.start_fran_name                  --始发加盟商名称
        ,main.start_agent_code                 --始发代理区编码
        ,main.start_agent_name                 --始发代理区名称
        ,main.start_center_code                --始发中心编码
        ,main.start_center_name                --始发中心名称
        ,main.end_network_code                 --目的网点编码
        ,main.end_network_name                 --目的网点名称
        ,main.end_city_code                    --目的城市编码
        ,main.end_city_name                    --目的城市名称
        ,main.end_provider_code                --目的省份编码
        ,main.end_provider_name                --目的省份名称
        ,main.end_fran_code                    --目的加盟商编码
        ,main.end_fran_name                    --目的加盟商名称
        ,main.end_agent_code                   --目的代理区编码
        ,main.end_agent_name                   --目的代理区名称
        ,main.end_center_code                  --目的中心编码
        ,main.end_center_name                  --目的中心名称
        ,main.aging_sign_time                  --时效签收时间
        ,main.actual_sign_time                 --实际签收时间
        ,main.plan_sign_time
        ,main.delay_day - nvl(event.defer_day,0) as delay_day
        ,main.duty_main_id
        ,main.duty_main_agent_id
        ,main.start_network_id
        ,main.start_agent_id
        ,main.port_type --1进港,0出港
        ,main.center_type --中心类型1始发中心,2中转中心,3目的中心
        ,main.customer_code
        ,if(way.waybill_no is not null,1,0) as is_tyd
        ,if(cust.code is not null,1,0) as is_ntzk
        ,row_number() over (partition by main.waybill_no order by event.defer_day desc) rn
        ,to_date(main.aging_sign_time) as dt
    from (
        select
             waybill_no
            ,order_source_code
            ,order_source_name
            ,duty_type
            ,duty_main_code
            ,duty_main_name
            ,duty_main_type
            ,duty_main_agent_code
            ,duty_main_agent_name
            ,duty_provider_code
            ,duty_provider_desc
            ,static_scan_time
            ,dynamic_scan_time
            ,duty_shipment_no
            ,duty_shipment_name
            ,start_network_code
            ,start_network_name
            ,start_city_code
            ,start_city_name
            ,start_provider_code
            ,start_provider_name
            ,start_fran_code
            ,start_fran_name
            ,start_agent_code
            ,start_agent_name
            ,start_center_code
            ,start_center_name
            ,end_network_code
            ,end_network_name
            ,end_city_code
            ,end_city_name
            ,end_provider_code
            ,end_provider_name
            ,end_fran_code
            ,end_fran_name
            ,end_agent_code
            ,end_agent_name
            ,end_center_code
            ,end_center_name
            ,aging_sign_time
            ,actual_sign_time
            ,plan_sign_time
            ,case when duty_type in ('错发','中转环节-错分') then 1
                  else delay_day - postpone_day - line_sy_day end  as delay_day --延误天数
            ,duty_main_id
            ,duty_main_agent_id
            ,start_network_id
            ,start_agent_id
            ,port_type --1进港,0出港
            ,center_type --中心类型1始发中心,2中转中心,3目的中心
            ,customer_code
        from (
            select
                 *
                ,sum(case when postpone_type = 2 and all_problem_date is not null and scan_time <= all_problem_date then 1
                          when postpone_type = 2 and all_problem_date is null and scan_time <= date_sub(to_date(dynamic_scan_time),1) then 1
                          else 0 end) over(partition by waybill_no) as postpone_day --顺延天数
                ,case when all_problem_date is null then datediff(dynamic_scan_time,static_scan_time)
                      when all_problem_date is not null then datediff(all_problem_date,static_scan_time)
                      end as delay_day --延误天数
                ,dt
            from (
                select
                     main.*
                    ,sqs.postpone_type
                    ,sqs.scan_time
                    ,first_value(if(sqs.postpone_type = 3,sqs.scan_time,null)) over(partition by main.waybill_no order by sqs.postpone_type desc,sqs.scan_time ) as all_problem_date
                from (
                    select
                         main.waybill_no
                        ,main.order_source_code             as order_source_code
                        ,main.order_source_name             as order_source_name
                        ,main.ziyou_duty_type               as duty_type
                        ,dim_duty.id                        as duty_main_id
                        ,main.ziyou_duty_main_code          as duty_main_code
                        ,main.ziyou_duty_main_name          as duty_main_name
                        ,main.ziyou_duty_main_type          as duty_main_type
                        ,dim_duty.agent_id                  as duty_main_agent_id
                        ,main.ziyou_duty_main_agent_code    as duty_main_agent_code
                        ,main.ziyou_duty_main_agent_name    as duty_main_agent_name
                        ,main.ziyou_duty_provider_code      as duty_provider_code
                        ,main.ziyou_duty_provider_desc      as duty_provider_desc
                        ,main.zy_static_scan_time           as static_scan_time
                        ,main.zy_dynamic_scan_time          as dynamic_scan_time
                        ,main.ziyou_duty_shipment_no        as duty_shipment_no
                        ,main.ziyou_duty_shipment_name      as duty_shipment_name
                        ,dim_start.id                       as start_network_id
                        ,main.start_network_code            as start_network_code
                        ,main.start_network_name            as start_network_name
                        ,main.start_city_code               as start_city_code
                        ,main.start_city_name               as start_city_name
                        ,main.start_provider_code           as start_provider_code
                        ,main.start_provider_name           as start_provider_name
                        ,main.start_fran_code               as start_fran_code
                        ,main.start_fran_name               as start_fran_name
                        ,dim_start.agent_id                 as start_agent_id
                        ,main.start_agent_code              as start_agent_code
                        ,main.start_agent_name              as start_agent_name
                        ,main.start_center_code             as start_center_code
                        ,main.start_center_name             as start_center_name
                        ,main.end_network_code              as end_network_code
                        ,main.end_network_name              as end_network_name
                        ,main.end_city_code                 as end_city_code
                        ,main.end_city_name                 as end_city_name
                        ,main.end_provider_code             as end_provider_code
                        ,main.end_provider_name             as end_provider_name
                        ,main.end_fran_code                 as end_fran_code
                        ,main.end_fran_name                 as end_fran_name
                        ,main.end_agent_code                as end_agent_code
                        ,main.end_agent_name                as end_agent_name
                        ,main.end_center_code               as end_center_code
                        ,main.end_center_name               as end_center_name
                        ,main.aging_sign_time               as aging_sign_time
                        ,main.actual_sign_time              as actual_sign_time
                        ,main.ziyou_plan_sign_time          as plan_sign_time
                        ,main.customer_code                 as customer_code
                        ,case when line.send_collect = 1 then 1 else 0 end as line_sy_day --线路顺延天数
                        ,case when ziyou_duty_type = '进港转运不及时' then 1 else 0 end as port_type --1进港,0出港
                        ,case when ziyou_duty_main_code = start_center_code then 1
                              when ziyou_duty_main_code = end_center_code   then 3
                              else 2 end as center_type --中心类型1始发中心,2中转中心,3目的中心
                        ,to_date(main.ziyou_plan_sign_time) as dt
                    from jms_dm.dm_whole_decide_duty_pres_base_dt main
                    left join jms_dim.dim_network_whole_massage dim_start
                       on dim_start.code = main.start_network_code
                    left join jms_dim.dim_network_whole_massage dim_duty
                       on dim_duty.code = main.ziyou_duty_main_code
                    left join (
                       select actual_shipment_no
                       from jms_dwd.dwd_tms_temp_transport_base_dt --临时转运表
                       where dt between date_sub('{{ execution_date | cst_ds }}',15)  and '{{ execution_date | cst_ds }}'
                       group by actual_shipment_no
                    ) temp_trans on temp_trans.actual_shipment_no = main.ziyou_duty_shipment_no
                    left join (
                        select b.task_no as shipment_no
                        from jms_dim.dim_sys_notice_base a --调度指令表
                        left join (
                            select
                                 notice_id
                                ,task_no
                                ,row_number() over (partition by notice_id,task_no order by dt) rn
                            from jms_dim.dim_adjust_detail_dt
                            where dt between date_sub('{{ execution_date | cst_ds }}',10) and '{{ execution_date | cst_ds }}'
                            and is_delete = 1
                            and adjust_type in ('AD01','AD02','AD03') --运力操作
                        ) b on a.id = b.notice_id and b.rn = 1
                        where to_date(a.create_time) between date_sub('{{ execution_date | cst_ds }}',10) and '{{ execution_date | cst_ds }}'
                        and a.review_status = 1 --1 复核通过
                        and b.task_no is not null
                        group by b.task_no
                    ) adjust on adjust.shipment_no = main.ziyou_duty_shipment_no
                    left join (
                        select
                             code
                            ,name
                            ,end_code
                            ,send_collect
                        from jms_dim.dim_tmsnew_vehicle_line dim
                        where is_enable = 1
                    ) line on line.name = main.ziyou_duty_shipment_name
                          and line.end_code = main.ziyou_duty_main_code
                    left join (
                        select waybill_no
                        from jms_dwd.dwd_tab_reback_transfer_express_base_dt
                        where dt between date_sub('{{ execution_date | cst_ds }}',21) and '{{ execution_date | cst_ds }}'
                          and print_flag = '1' --面单是否已打印 1是 0否
                        group by waybill_no
                    ) reback on reback.waybill_no = main.waybill_no
                    where dt = '{{ execution_date | cst_ds }}'
                      and ziyou_duty_main_type = 4 --中心
                      and aging_sign_time > ziyou_plan_sign_time
                      and ziyou_duty_type is not null
                      and temp_trans.actual_shipment_no is null --剔除临时调度
                      and main.zy_scan_site_actual_arrival_time < main.ziyou_planned_arrival_time --剔除实际比计划晚
                      and main.ziyou_duty_type <> '运输晚点'
                      and main.ziyou_duty_type <> '备用路由'
                      and adjust.shipment_no is null --剔除运力操作的数据
                      and reback.waybill_no is null --剔除退转件
                      and !(main.ziyou_duty_main_bagg_user_name like '%交叉带%' and main.ziyou_duty_type in ('中转环节-错分','错发'))
                ) main
                left join (
                    select
                         waybill_no
                        ,registration_network_code
                        ,second_level_type_code
                        ,postpone_type
                        ,postpone_name
                        ,scan_time
                    from (
                        select
                             sqs.waybill_no
                            ,sqs.registration_network_code --登记网点
                            ,sqs.second_level_type_code --二级类型
                            ,to_date(sqs.scan_time) as scan_time --创建时间
                            ,dim.postpone_type
                            ,case when dim.postpone_type=1 then '不顺延'
                                  when dim.postpone_type=2 then '当天有效'
                                  when dim.postpone_type=3 then '全部有效'
                                  end as  postpone_name
                            --去重优先取全部有效然后当天有效
                            ,row_number() over(partition by sqs.waybill_no,sqs.registration_network_code,to_date(sqs.scan_time) order by dim.postpone_type desc,sqs.scan_time desc) as rk
                        from jms_dwd.dwd_sqs_registration_problem_piece_base_dt sqs --问题件表
                        inner join jms_dim.dim_problem_piece_type_base dim
                            on sqs.second_level_type_code = dim.code
                           and dim.is_enable = 1
                           and dim.postpone_type != 1
                        where sqs.dt between date_sub('{{ execution_date | cst_ds }}',13) and '{{ execution_date | cst_ds }}'
                    ) a where rk = 1
                ) sqs on sqs.waybill_no = main.waybill_no
                     and sqs.registration_network_code = main.duty_main_code
                     and sqs.scan_time <= main.dynamic_scan_time
            ) a
        ) a group by waybill_no
                    ,order_source_code
                    ,order_source_name
                    ,duty_type
                    ,duty_main_code
                    ,duty_main_name
                    ,duty_main_type
                    ,duty_main_agent_code
                    ,duty_main_agent_name
                    ,duty_provider_code
                    ,duty_provider_desc
                    ,static_scan_time
                    ,dynamic_scan_time
                    ,duty_shipment_no
                    ,duty_shipment_name
                    ,start_network_code
                    ,start_network_name
                    ,start_city_code
                    ,start_city_name
                    ,start_provider_code
                    ,start_provider_name
                    ,start_fran_code
                    ,start_fran_name
                    ,start_agent_code
                    ,start_agent_name
                    ,start_center_code
                    ,start_center_name
                    ,end_network_code
                    ,end_network_name
                    ,end_city_code
                    ,end_city_name
                    ,end_provider_code
                    ,end_provider_name
                    ,end_fran_code
                    ,end_fran_name
                    ,end_agent_code
                    ,end_agent_name
                    ,end_center_code
                    ,end_center_name
                    ,aging_sign_time
                    ,actual_sign_time
                    ,plan_sign_time
                    ,delay_day - postpone_day - line_sy_day
                    ,duty_main_id
                    ,duty_main_agent_id
                    ,start_network_id
                    ,start_agent_id
                    ,port_type --1进港,0出港
                    ,center_type --中心类型1始发中心,2中转中心,3目的中心
                    ,customer_code
    ) main
    left join (
        select
             code
            ,name
            ,create_time
            ,to_date(create_time) as create_date
            ,date_add(to_date(create_time),15)  as end_date
        from jms_dim.dim_network_whole_massage  --网点资料，豁免开往+15天
        where is_suspend = 1 or is_enable <> 1
    ) dim on main.duty_main_code = dim.code
    left join (--剔除异常事件登记数据
        select
             involved_org_code as involved_org_code --涉事组织编码
            ,involved_org_name as involved_org_name --涉事组织名称
            ,defer_day as defer_day       --上报时间
            ,to_date(happen_time) as happen_time  --发生时间
            ,to_date(end_time   ) as end_time     --结束时间
            ,in_out_port_type   --进出港类型 0进港 1出港 2进出港
            --,row_number() over(partition by involved_org_code order by defer_day desc) as rn
        from jms_dim.dim_abnormal_event_report_base --异常事件报表
        where to_date(happen_time) between date_sub('{{ execution_date | cst_ds }}',9) and '{{ execution_date | cst_ds }}'
    ) event on main.duty_main_code = event.involved_org_code
           and to_date(main.static_scan_time) between happen_time and end_time
           and case when main.port_type = 1 and event.in_out_port_type = 0 then 1 
                    when main.port_type = 0 and event.in_out_port_type = 1 then 1 
                    when event.in_out_port_type = 2 then 1 else 0 end = 1 
    left join (
        select
              get_json_object( json, '$.networkCode') network_code,
              get_json_object( json, '$.networkName') network_name
        from jms_dim.dim_arbitration_configuration_base
        lateral view explode(split(regexp_replace(regexp_replace(network_info , '\\[|\\]',''),'\\}\\, \\{','\\}\\;\\{'),'\\;'))  jsons AS json
        where '{{ execution_date | cst_ds }}' between start_date and end_date
          and is_enable = 1
          and configuration_type = 1
    ) arbit on arbit.network_code = main.duty_main_code
    left join (
       select waybill_no 
       from jms_dwd.dwd_yl_oms_oms_waybill_incre_dt 
       where dt between date_sub('{{ execution_date | cst_ds }}', 15) and '{{ execution_date | cst_ds }}' 
       and express_type_code = 'TYD'
    ) way on way.waybill_no = main.waybill_no
    left join ( 
        select 
             code
            ,name 
        from jms_dim.dim_lmdm_sys_customer_addition
        where is_enable = 1
        and is_delete = 1
        and customer_category = 2
    ) cust on cust.code = main.customer_code
    left join ( -- 获取订单标签
        select
             a.waybill_id as waybill_no
        from jms_dwd.dwd_yl_oms_order_mark_expand_base_dt a
        left join jms_dim.dim_yl_oms_system_dict_base b
           on a. field_name = b.label 
          and b.is_delete = 0
        where a.dt between  date_sub('{{ execution_date | cst_ds }}', 15) and '{{ execution_date | cst_ds }}' 
        and b.value = '桃花岛逆向上门取件'
        group by a.waybill_id
    ) mark on mark.waybill_no = main.waybill_no
    where dim.code is null --剔除暂停、禁用网点
    and arbit.network_code is null
    and mark.waybill_no is null --剔除桃花岛逆向上门取件
) main
where main.rn = 1
  and main.delay_day > 0  --剔除延迟天数=0的单号
distribute by pmod(hash(rand()),5);
